MySQL MariaDB AES-ENCRYPT
- #mysql, mariadb
- 17 Aug 2020
Both MySQL and MariaDB provide a native way to encrypt and decrypt data using the AES algorithm. I am told by experienced programmers that is not a good idea to store password this way (unless they are hashed) because the decryption key might appear in the logs or in transit but just in case here is how is done:
Let's assume we have a table with admins for a website. We can now encrypt the password field directly in the database:
INSERT INTO `admins` (`id`, `user`, `pw`) VALUES (NULL, 'admin',
AES_ENCRYPT('user_password','encryption_key'));It is pretty straightforward: the password record will be encrypted with an encryption key. The AES encrypted record is stored in binary format so in my table the password column type is varbinary(150).
Now to decrypt your password you need to provide the encryption key you used to add the data (without the key you won't be able to decrypt your data for the encrypted field/row so don't lose it!)
SELECT *, AES_DECRYPT(password, 'encryption_key') AS `decrypted` FROM admins WHERE user = 'admin'This will return the decrypted password (or data). The only usage I could think of is probably not very secure but probably still better than storing passwords in clear in the database đ¤.
I think this can be be useful and since I didn't find many recent articles with examples working on MariaDB and MySQL I wanted to share the easiest way to use it!
*UPDATE*
this is just a proof of concept of how decryption works in the database. You should still store just the hash of a password and not the password in clear (even if you use AES_ENCRYPT (but using both will probably be an extra layer of security đ§